﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WindowsFormsApp1.Mode;

namespace SwaySway.Dao.Base
{
    public class DepperSqlHelp<TEntity>
    {

        /// <summary>
        /// 获取更新的字符串的字符串
        /// </summary>
        /// <param name="entity">实体模型->字段必须对应</param>
        /// <param name="TableName">更新的表名</param>
        /// <param name="WhereStr">更新条件</param>
        /// <returns></returns>
        public bool Update(TEntity entity, string TableName, string WhereStr = "")
        {
            List<string> list;
            var parament = GetParament(entity, out list);
            var agg = list.Aggregate(new StringBuilder(), (x, y) =>
            {
                x.Append(y);
                x.Append("=@");
                x.Append(y);
                x.Append(",");
                return x;
            });
            string tempWhere = " where 1=1 ";
            if (!string.IsNullOrEmpty(WhereStr))
            {
                tempWhere = WhereStr;
            }
            var sql = $"UPDATE [{TableName}] SET {agg.ToString().TrimEnd(',')} " + tempWhere;

            int result = 0;
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                result = conn.Execute(sql, parament, commandType: CommandType.Text);
            }
            return result > 0 ? true : false;
        }

        public bool Insert(TEntity entity, string TableName)
        {
            List<string> list;
            var parament = GetParament(entity, out list);
            var sql = $"INSERT INTO [{TableName}] ({string.Join(",", list)}) VALUES (@{string.Join(",@", list)});SELECT @@IDENTITY";
            int result = 0;
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                result = conn.Query<int>(sql, parament).FirstOrDefault();
            }

            return result > 0 ? true : false;
        }


		public EntityPage<T> Pagination<T>(string sql, int currentIndex, int pageSize, bool sumCount, string translate = "*", string orderBy = "Id", object parament = null)
		{
			var excuteSql = GetPaginationSql(sql, currentIndex, pageSize, translate, orderBy) + $";SELECT A.COUNT FROM ( SELECT COUNT(1) AS COUNT FROM {sql}) A";

			try
			{

			
			using (IDbConnection conn = SqlConn.OpenConnectionStar())
			{
				var result = conn.QueryMultiple(excuteSql, parament, commandType: CommandType.Text);

				EntityPage<T> Return = new EntityPage<T>();
				Return.Items = result.Read<T>().ToList();
				Return.Count = result.Read<int>().Single();
				Return.PageCount = Return.Count > 0 ? (Return.Count - 1) / pageSize + 1 : 0;
				return Return;
			}
			}
			catch (Exception e)
			{

				throw;
			}
		}


		/// <summary>
		/// 插入返回自增列
		/// </summary>
		/// <param name="entity"></param>
		/// <param name="TableName"></param>
		/// <returns></returns>
		public int InsertReturn(TEntity entity, string TableName)
        {
            List<string> list;
            var parament = GetParament(entity, out list);
            var sql = $"INSERT INTO [{TableName}] ({string.Join(",", list)}) VALUES (@{string.Join(",@", list)});SELECT @@IDENTITY";
            int result = 0;
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                result = conn.Query<int>(sql, parament).FirstOrDefault();
            }

            return result;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="TableName">需要删除的表名</param>
        /// <param name="where">删除条件 where以及后部分</param>
        /// <returns></returns>
        public bool Delete(string TableName, string where, object p = null)
        {
            var sql = $"delete from [{ TableName }] {where}";
            int result = 0;
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                result = conn.Execute(sql, p, commandType: CommandType.Text);
            }
            return result > 0 ? true : false;
        }


        public static string GetPaginationSql(
            string sql,
            int currentIndex,
            int pageSize,
            string translate = "*",
            string orderBy = "Id"
           )
        {
            var start = currentIndex == 0 || currentIndex == 1 ? 1 : ((currentIndex - 1) * pageSize) + 1;
            return $"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {orderBy})NewRow ,{translate} FROM {sql} ) AUS WHERE NewRow BETWEEN {start} AND {start + pageSize - 1}";
        }


        public DynamicParameters GetParament<T>(T t, out List<string> list)
        {
            list = new List<string>();
            var parament = new DynamicParameters();
            foreach (var node in typeof(T).GetProperties())
            {
                if (node.Name.ToLower() == "id" || node.GetValue(t) == null) continue;
                list.Add(node.Name);
                parament.Add("@" + node.Name, node.GetValue(t));
            }
            return parament;
        }

    }
}
